package com.zyf;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.joda.time.DateTime;
import org.junit.Test;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Date;

public class ExcelReadTest {

    String path= "C:\\Users\\zyf\\Desktop/";

    @Test
    public void testRead() throws Exception {
        //获取文件流
        FileInputStream inputStream = new FileInputStream(path+"2-11月出货表-1.xls");

        //得到一个工具薄
        Workbook workbook = new HSSFWorkbook(inputStream);
        //得到表
        Sheet sheetAt = workbook.getSheetAt(0);
        //得到行
        Row row = sheetAt.getRow(2);
        //得到列
        Cell cell = row.getCell(6);


        //getStringCellValue  获取字符串类型
        //System.out.println(cell.getStringCellValue());

        //getDateCellValue  获取时间类型
        //读取值的时候注意字符类型
        Date dateCellValue = cell.getDateCellValue();
        String s = new DateTime(dateCellValue).toString("yyyy-MM-dd");
        System.out.println(s);

        inputStream.close();
    }

    @Test
    public void ReadList() throws IOException {
        //获取文件流
        FileInputStream inputStream = new FileInputStream(path+"2-11月出货表-1.xls");

        //得到一个工具薄
        Workbook workbook = new HSSFWorkbook(inputStream);
        Sheet sheet = workbook.getSheetAt(0);
        //获取标题内容
        Row rowTitle = sheet.getRow(1);
        if (rowTitle!=null){
            int number = rowTitle.getPhysicalNumberOfCells();
            for (int i = 0; i <= number; i++) {
                Cell cell = rowTitle.getCell(i);
                if (cell!=null){
                    int cellType = cell.getCellType();
                    String cellValue = cell.getStringCellValue();
                    System.out.print(cellValue+" | ");
                }
            }
            System.out.println();
        }
        //获取表中的内容
        int numberOfRows = sheet.getPhysicalNumberOfRows();
        for (int i = 2; i <=numberOfRows ; i++) {
            Row row = sheet.getRow(i);
            if (row!=null){
                //读取行中的列
                int number = rowTitle.getPhysicalNumberOfCells();
                for (int j = 0; j <=number; j++) {
                    Cell cell = row.getCell(j);
                    //匹配列的数据类型
                    if (cell!=null){
                        int cellType = cell.getCellType();
                        String cellValue="";
                        switch (cellType){
                            case HSSFCell.CELL_TYPE_STRING: //字符串
                                cellValue= cell.getStringCellValue();
                                break;
                            case HSSFCell.CELL_TYPE_BOOLEAN: //boolean类型
                                cellValue= String.valueOf(cell.getBooleanCellValue());
                                break;
                            case HSSFCell.CELL_TYPE_BLANK: //空 null
                                break;
                            case HSSFCell.CELL_TYPE_NUMERIC: //数字（日期、普通数字）
                                if (HSSFDateUtil.isCellDateFormatted(cell)){//日期
                                    Date date =cell.getDateCellValue();
                                    cellValue = new DateTime(date).toString("yyyy-MM-dd");
                                }else {
                                    //不是日期格式，防止数字过长
                                    cell.setCellValue(HSSFCell.CELL_TYPE_STRING);
                                    cellValue= cell.toString();
                                }
                                break;
                            case HSSFCell.CELL_TYPE_ERROR: //错误
                                System.out.println("数据类型错误");
                                break;
                        }
                        System.out.print(cellValue+" | ");
                    }
                }
            }
        }
        inputStream.close();
    }

    //获取计算公式里的值
    @Test
    public  void testFormula()throws IOException{
        String path="C:\\Users\\zyf\\Documents\\Tencent Files\\2878932916\\FileRecv/";
        FileInputStream inputStream = new FileInputStream(path+"1-购销合同.xls");
        Workbook workbook = new HSSFWorkbook(inputStream);
        Sheet sheet = workbook.getSheet("购销合同");

         Row row = sheet.getRow(10);
        Cell cell = row.getCell(8);

        //拿到计算公式  eval
        FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);

        //输出单元格的内容
        int type = cell.getCellType();
        switch (type){
            case Cell.CELL_TYPE_FORMULA: //公式
                String formula = cell.getCellFormula();
                System.out.println(formula);

                //计算
                CellValue evaluate = formulaEvaluator.evaluate(cell);
                String format = evaluate.formatAsString();
                System.out.println(format);
                break;
        }
        inputStream.close();
    }
}
